-- 数据库初始化脚本


-- 创建文章表
CREATE TABLE IF NOT EXISTS articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(50),
    theme VARCHAR(100),
    keywords TEXT,
    content TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'draft',
    author_id INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP,
    checksum VARCHAR(64),
    metadata JSONB
);

-- 创建媒体文件表
CREATE TABLE IF NOT EXISTS media_files (
    id SERIAL PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255),
    file_path VARCHAR(500),
    file_size BIGINT,
    mime_type VARCHAR(100),
    uploader_id INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_public BOOLEAN DEFAULT true,
    metadata JSONB
);

-- 创建搜索索引表
CREATE TABLE IF NOT EXISTS search_index (
    id SERIAL PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INTEGER NOT NULL,
    content TEXT,
    keywords TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(entity_type, entity_id)
);

-- 创建数据同步日志表
CREATE TABLE IF NOT EXISTS sync_logs (
    id SERIAL PRIMARY KEY,
    operation VARCHAR(20) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INTEGER NOT NULL,
    old_data JSONB,
    new_data JSONB,
    sync_status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP,
    error_message TEXT
);

-- 创建索引以提高查询性能
CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status);
CREATE INDEX IF NOT EXISTS idx_articles_category ON articles(category);
CREATE INDEX IF NOT EXISTS idx_articles_created_at ON articles(created_at);
CREATE INDEX IF NOT EXISTS idx_search_index_content ON search_index USING gin(to_tsvector('english', content));
CREATE INDEX IF NOT EXISTS idx_sync_logs_entity ON sync_logs(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_sync_logs_created_at ON sync_logs(created_at);

-- 插入默认管理员用户
INSERT INTO users (username, email, password, role) 
VALUES ('admin', 'admin@example.com', '$2a$12$LQv3c1yqBWVHrnG6p3QnCOB4CJZ7w6Y8Yk8YJ8YJ8YJ8YJ8YJ8YJ8Y', 'admin')
ON CONFLICT (username) DO NOTHING;

-- 创建全文搜索函数
CREATE OR REPLACE FUNCTION search_articles(query TEXT)
RETURNS TABLE(id INTEGER, title VARCHAR, content TEXT, rank REAL) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        a.id,
        a.title,
        a.content,
        ts_rank(
            to_tsvector('english', COALESCE(a.title, '') || ' ' || COALESCE(a.content, '')),
            plainto_tsquery('english', query)
        ) as rank
    FROM articles a
    WHERE a.status = 'published'
    AND to_tsvector('english', COALESCE(a.title, '') || ' ' || COALESCE(a.content, '')) @@ plainto_tsquery('english', query)
    ORDER BY rank DESC;
END;
$$ LANGUAGE plpgsql;